--    Program: 		CAPPOIMP_POSITION_HIERARCHY_ELEMENT.sql
--    Description:  Import PO POSITION HIERACHY ELEMENT information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         23-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_pos_structure_version_id  number;
   l_pos_structure_element_id  number;
   l_subordinate_position_id   number;
   l_parent_position_id        number;
   l_position_id				number;
                    		                 		
   cursor cur_per_pos_structure_elements is
      select * from CRST_PER_POS_STRUCTURE_ELEMENT;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_per_pos_structure_elements loop   	
     l_err_msg_tmp := '';
     
     
     SELECT PER_POS_STRUCTURE_ELEMENTS_S.NEXTVAL 
     INTO   l_pos_structure_element_id
     FROM DUAL;
     
     select per_positions_s.nextval
     into l_position_id
     from dual;
     
     /*SELECT CAPBRIMP_MAPPING.GetBGMapping(CUR.BUSINESS_GROUP_ID)
     INTO l_bus_grp_id
     FROM DUAL;*/
     
     
     l_pos_structure_version_id := null;
     if cur.POS_STRUCTURE_VERSION_ID is not null then
   	   begin
   	   	 select pos_structure_version_id_n 
   	   	 into   l_pos_structure_version_id
         from   crst_per_pos_structure_version
         where  pos_structure_version_id = cur.POS_STRUCTURE_VERSION_ID;
	   exception
	   	 when others then
	   	   l_err_msg_tmp := 'Position Hierarchy Structure Version '||cur.POS_STRUCTURE_VERSION_ID||' not found'; 	
	   end;	   	
     end if;
     
     l_subordinate_position_id := null;
     if cur.SUBORDINATE_POSITION_ID is not null then
   	   begin
   	   	 select position_id_n 
   	   	 into   l_subordinate_position_id
         from   crst_per_all_positions
         where  position_id = cur.SUBORDINATE_POSITION_ID;
	   exception
	   	 when others then
	   	   l_err_msg_tmp := 'Subordinate Position '||cur.SUBORDINATE_POSITION_ID||' not found'; 	
	   end;	   	
     end if;
     
     l_parent_position_id := null;
     if cur.PARENT_POSITION_ID is not null then
   	   begin
   	   	 select position_id_n 
   	   	 into   l_parent_position_id
         from   crst_per_all_positions
         where  position_id = cur.PARENT_POSITION_ID;
	   exception
	   	 when others then
	   	   l_err_msg_tmp := 'Parent Position '||cur.PARENT_POSITION_ID||' not found'; 	
	   end;	   	
     end if;
     
     begin
       -- import data
       hr_pos_hierarchy_ele_api.create_pos_hierarchy_ele
                       (p_parent_position_id            => l_parent_position_id
                       ,p_pos_structure_version_id      => l_pos_structure_version_id
                       ,p_subordinate_position_id       => l_position_id
                       ,p_business_group_id             => CAPBRIMP_MAPPING.GetBGMapping(CUR.BUSINESS_GROUP_ID) --l_bus_grp_id
                       ,p_hr_installed                  => NULL
                       ,p_effective_date                => SYSDATE
                       ,p_pos_structure_element_id      => l_pos_structure_element_id
                       ,p_object_version_number         => CUR.object_version_number
                       );
                       EXCEPTION
                     		when others then
     	 					l_err_msg_tmp := sqlerrm;
     	end;
       /*insert into PER_POS_STRUCTURE_ELEMENTS
       (
          POS_STRUCTURE_ELEMENT_ID  ,
          BUSINESS_GROUP_ID         ,
          POS_STRUCTURE_VERSION_ID  ,
          SUBORDINATE_POSITION_ID   ,
          PARENT_POSITION_ID        ,
          REQUEST_ID                ,
          PROGRAM_APPLICATION_ID    ,
          PROGRAM_ID                ,
          PROGRAM_UPDATE_DATE       ,
          LAST_UPDATE_DATE          ,
          LAST_UPDATED_BY           ,
          LAST_UPDATE_LOGIN         ,
          CREATED_BY                ,
          CREATION_DATE             ,
          OBJECT_VERSION_NUMBER            
       )
       values
       (
          l_pos_structure_element_id    ,
          CAPBRIMP_MAPPING.GetBGMapping(cur.BUSINESS_GROUP_ID),
          --l_pos_structure_version_id   ,
          l_pos_structure_version_id,
          l_subordinate_position_id     ,
          l_parent_position_id          ,
          cur.REQUEST_ID                ,
          cur.PROGRAM_APPLICATION_ID    ,
          cur.PROGRAM_ID                ,
          cur.PROGRAM_UPDATE_DATE       ,
          cur.LAST_UPDATE_DATE          ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
          cur.LAST_UPDATE_LOGIN         ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
          cur.CREATION_DATE             ,
          cur.OBJECT_VERSION_NUMBER     
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;*/
     
	 if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  
   end loop;

   if l_rejected_count = 0 then
   	  dbms_output.put_line('2');
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_POSITION_HIERARCHY_ELEMENT', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
